﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using motelmanagement.common;
using System.Data;
using System.Data.OleDb;
namespace motelmanagement.dao
{
    class RoomCategoryDAO
    {
        SqlUtil sqlUtil = new SqlUtil();
        OleDbCommand cmd = null;
        private string strSql = null;

        public DataTable getAllRoomCategory() {
            strSql = "SELECT Room_category.room_cate_id, Room_category.room_cate_name, Room_category.room_cate_people";
            strSql+=", Room_category.room_cate_bed, Room_category.room_cate_note, Room_category.room_price_id";
            strSql+=", Room_price.room_price_name, Room_category.service_id, Service.service_name";
            strSql+=" FROM Service INNER JOIN (Room_category INNER JOIN Room_price";
            strSql+=" ON Room_category.room_price_id = Room_price.room_price_id)";
            strSql += " ON Service.service_id = Room_category.service_id;";
            cmd = new OleDbCommand(strSql);
            sqlUtil.Load(cmd);
            return sqlUtil;
        }

        public DataTable getRoomCategory(int id)
        {
            strSql = "SELECT Room_category.room_cate_id, Room_category.room_cate_name, Room_category.room_cate_people";
            strSql += ", Room_category.room_cate_bed, Room_category.room_cate_note, Room_category.room_price_id";
            strSql += ", Room_price.room_price_name, Room_category.service_id, Service.service_name";
            strSql += " FROM Service";
            strSql += " INNER JOIN (Room_category INNER JOIN Room_price ON Room_category.room_price_id = Room_price.room_price_id)";
            strSql += " ON Service.service_id = Room_category.service_id";
            strSql += " WHERE Room_category.room_cate_id=@id";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("id", SqlDbType.Int).Value = id;
            sqlUtil.Load(cmd);
            return sqlUtil;
        }
        public void insertRoomCategory(string roomCateName,int roomCatePeople,int roomCateBed,
            string roomCateNote,int roomPriceId,int serviceId) {
                strSql = "INSERT INTO Room_category(room_cate_name,room_cate_people,room_cate_bed";
                strSql += ",room_cate_note,room_price_id,service_id)";
                strSql += " VALUES(@name,@people,@bed,@note,@price,@service)";
                cmd = new OleDbCommand(strSql);
                cmd.Parameters.Add("name",SqlDbType.NText).Value=roomCateName;
                cmd.Parameters.Add("people", SqlDbType.Int).Value = roomCatePeople;
                cmd.Parameters.Add("bed", SqlDbType.Int).Value = roomCateBed;
                cmd.Parameters.Add("note", SqlDbType.NText).Value = roomCateNote;
                cmd.Parameters.Add("price", SqlDbType.Int).Value = roomPriceId;
                cmd.Parameters.Add("service", SqlDbType.Int).Value = serviceId;
                sqlUtil.ExecuteNoneQuery(cmd);
        }
        public void updateRoomCategory(int roomCateId,string roomCateName, int roomCatePeople, int roomCateBed,
           string roomCateNote, int roomPriceId, int serviceId)
        {
            strSql = "UPDATE Room_category SET room_cate_name=@name,room_cate_people=@people";
            strSql += ",room_cate_bed=@bed,room_cate_note=@note,room_price_id=@price,service_id=@service)";
            strSql += " WHERE room_category_id=@id";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("name", SqlDbType.NText).Value = roomCateName;
            cmd.Parameters.Add("people", SqlDbType.Int).Value = roomCatePeople;
            cmd.Parameters.Add("bed", SqlDbType.Int).Value = roomCateBed;
            cmd.Parameters.Add("note", SqlDbType.NText).Value = roomCateNote;
            cmd.Parameters.Add("price", SqlDbType.Int).Value = roomPriceId;
            cmd.Parameters.Add("service", SqlDbType.Int).Value = serviceId;
            cmd.Parameters.Add("id", SqlDbType.Int).Value = roomCateId;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
        public void deleteRoomCategory(int roomCateId)
        {
            strSql = "DELETE FROM Room_category WHERE room_category_id=@id";
            cmd = new OleDbCommand(strSql);
            cmd.Parameters.Add("id", SqlDbType.Int).Value = roomCateId;
            sqlUtil.ExecuteNoneQuery(cmd);
        }
    }
}
